package com.bootdo.common.utils.excel;

import com.bootdo.common.utils.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import java.util.List;
import java.util.Map;

/**
 * Created by HP on 2019/4/12.
 */
public class MemberMoneyExcelUtils {

    /**
     * 将用户的余额信息导入到excel文件中去
     * @param orderList 余额记录列表
     * @param out 输出表
     */
    public static int exportUserExcel(List<Map<String,Object>> orderList,ServletOutputStream out)
    {
        try{
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//起始行,结束行,起始列,结束列
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //1.4 内容样式
            /*HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setWrapText(true); //自动换行 */
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("用户余额记录");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(20);
            //设置列宽
            /*sheet.setColumnWidth(0, 17);
            sheet.setColumnWidth(1, 10);
            sheet.setColumnWidth(2, 20);
            sheet.setColumnWidth(3, 23);
            sheet.setColumnWidth(4, 20);
            sheet.setColumnWidth(5, 5);
            sheet.setColumnWidth(6, 7);
            sheet.setColumnWidth(7, 17);
            sheet.setColumnWidth(8, 25);
            sheet.setColumnWidth(9, 15);
            sheet.setColumnWidth(10, 20);*/


            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);

            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("余额记录 ");

            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            //String[] titles = {"订单号","待发货","金额","商品名","货品规格","SKU","数量","收件人姓名","收件人电话","收件人地址","买家留言"};
            String[] titles = {"订单号","金额","类型","时间"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            //4.操作单元格;将用户列表写入excel
            if(orderList != null)
            {
                for(int j=0;j<orderList.size();j++)
                {
                    Map<String,Object> map = orderList.get(j);
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j + 2);

                    HSSFCell cell0 = row3.createCell(0);
                    cell0.setCellValue(StringUtils.getString(map.get("order_sn")));
                    HSSFCell cell1 = row3.createCell(1);
                    cell1.setCellValue(StringUtils.getString(map.get("price")));
                    HSSFCell cell2 = row3.createCell(2);
                    String type = StringUtils.getString(map.get("type"));
                    if (type.equals("0")) {
                        cell2.setCellValue("消费");
                    } else if (type.equals("1")) {
                        cell2.setCellValue("提现");
                    } else if (type.equals("2")) {
                        cell2.setCellValue("充值");
                    } else if (type.equals("3")) {
                        cell2.setCellValue("返利");
                    }
                    /*HSSFCell cell3 = row3.createCell(3);
                    String status = StringUtils.getString(map.get("status"));
                    if (status.equals("0")) {
                        cell3.setCellValue("提现中");
                    } else if (status.equals("1")) {
                        cell3.setCellValue("提现成功");
                    }*/
                    HSSFCell cell3 = row3.createCell(3);
                    cell3.setCellValue(StringUtils.getString(map.get("create_at")));
                }
            }
            //5.输出
            workbook.write(out);
            workbook.close();
            return 1;
            //out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
            return 0;
        }
    }

    /*个人消费记录*/
    public static int exportShoppingExcel(List<Map<String,Object>> orderList,ServletOutputStream out)
    {
        try{
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,4);//起始行,结束行,起始列,结束列
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //1.4 内容样式
            /*HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setWrapText(true); //自动换行 */
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("用户消费记录");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(25);
            //设置列宽
            /*sheet.setColumnWidth(0, 17);
            sheet.setColumnWidth(1, 10);
            sheet.setColumnWidth(2, 20);
            sheet.setColumnWidth(3, 23);
            sheet.setColumnWidth(4, 20);
            sheet.setColumnWidth(5, 5);
            sheet.setColumnWidth(6, 7);
            sheet.setColumnWidth(7, 17);
            sheet.setColumnWidth(8, 25);
            sheet.setColumnWidth(9, 15);
            sheet.setColumnWidth(10, 20);*/


            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);

            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("余额记录 ");

            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            //String[] titles = {"订单号","待发货","金额","商品名","货品规格","SKU","数量","收件人姓名","收件人电话","收件人地址","买家留言"};
            String[] titles = {"订单号","金额","订单类型","订单状态","时间"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            //4.操作单元格;将用户列表写入excel
            if(orderList != null)
            {
                for(int j=0;j<orderList.size();j++)
                {
                    Map<String,Object> map = orderList.get(j);
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j + 2);

                    HSSFCell cell0 = row3.createCell(0);
                    cell0.setCellValue(StringUtils.getString(map.get("order_sn")));
                    HSSFCell cell1 = row3.createCell(1);
                    cell1.setCellValue(StringUtils.getString(map.get("payment")));
                    HSSFCell cell2 = row3.createCell(2);
                    String type = StringUtils.getString(map.get("ordertype"));
                    if (type.equals("0")) {
                        cell2.setCellValue("普通订单");
                    } else if (type.equals("1")) {
                        cell2.setCellValue("秒杀订单");
                    } else if (type.equals("2")) {
                        cell2.setCellValue("9.9专区订单");
                    } else if (type.equals("3")) {
                        cell2.setCellValue("砍价订单");
                    }
                    HSSFCell cell3 = row3.createCell(3);
                    int status = Integer.valueOf(map.get("status").toString());
                    if (status == 0) {
                        cell3.setCellValue("砍价中");
                    } else if (status == 1) {
                        cell3.setCellValue("待支付");
                    } else if (status == 3) {
                        cell3.setCellValue("待发货");
                    } else if (status == 4) {
                        cell3.setCellValue("待收货");
                    } else if (status == 5) {
                        cell3.setCellValue("退货中");
                    } else if (status == 6) {
                        cell3.setCellValue("退货完成");
                    } else if (status == 7) {
                        cell3.setCellValue("已完成");
                    } else if (status == 8) {
                        cell3.setCellValue("订单取消");
                    } else if (status == 9) {
                        cell3.setCellValue("砍价失败");
                    }
                    HSSFCell cell4 = row3.createCell(4);
                    cell4.setCellValue(StringUtils.getString(map.get("create_at")));
                }
            }
            //5.输出
            workbook.write(out);
            workbook.close();
            return 1;
            //out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
            return 0;
        }
    }

    /*平台收入记录*/
    public static int exportPTShoppingExcel(List<Map<String,Object>> orderList,ServletOutputStream out)
    {
        try{
            //1.创建工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //1.1创建合并单元格对象
            CellRangeAddress callRangeAddress = new CellRangeAddress(0,0,0,7);//起始行,结束行,起始列,结束列
            //1.2头标题样式
            HSSFCellStyle headStyle = createCellStyle(workbook,(short)16);
            //1.3列标题样式
            HSSFCellStyle colStyle = createCellStyle(workbook,(short)13);
            //1.4 内容样式
            /*HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setWrapText(true); //自动换行 */
            //2.创建工作表
            HSSFSheet sheet = workbook.createSheet("用户消费记录");
            //2.1加载合并单元格对象
            sheet.addMergedRegion(callRangeAddress);
            //设置默认列宽
            sheet.setDefaultColumnWidth(25);
            //设置列宽
            /*sheet.setColumnWidth(0, 17);
            sheet.setColumnWidth(1, 10);
            sheet.setColumnWidth(2, 20);
            sheet.setColumnWidth(3, 23);
            sheet.setColumnWidth(4, 20);
            sheet.setColumnWidth(5, 5);
            sheet.setColumnWidth(6, 7);
            sheet.setColumnWidth(7, 17);
            sheet.setColumnWidth(8, 25);
            sheet.setColumnWidth(9, 15);
            sheet.setColumnWidth(10, 20);*/


            //3.创建行
            //3.1创建头标题行;并且设置头标题
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(0);

            //加载单元格样式
            cell.setCellStyle(headStyle);
            cell.setCellValue("余额记录 ");

            //3.2创建列标题;并且设置列标题
            HSSFRow row2 = sheet.createRow(1);
            //String[] titles = {"订单号","待发货","金额","商品名","货品规格","SKU","数量","收件人姓名","收件人电话","收件人地址","买家留言"};
            String[] titles = {"订单号","金额","消费类型","会员昵称","时间"};
            for(int i=0;i<titles.length;i++)
            {
                HSSFCell cell2 = row2.createCell(i);
                //加载单元格样式
                cell2.setCellStyle(colStyle);
                cell2.setCellValue(titles[i]);
            }
            //4.操作单元格;将用户列表写入excel
            if(orderList != null)
            {
                for(int j=0;j<orderList.size();j++)
                {
                    Map<String,Object> map = orderList.get(j);
                    //创建数据行,前面有两行,头标题行和列标题行
                    HSSFRow row3 = sheet.createRow(j + 2);

                    HSSFCell cell0 = row3.createCell(0);
                    cell0.setCellValue(StringUtils.getString(map.get("order_sn")));
                    HSSFCell cell1 = row3.createCell(1);
                    cell1.setCellValue(StringUtils.getString(map.get("price")));
                    HSSFCell cell2 = row3.createCell(2);
                    String type = StringUtils.getString(map.get("type"));
                    if (type.equals("0")) {
                        cell2.setCellValue("订单消费");
                    } else if (type.equals("1")) {
                        cell2.setCellValue("充值");
                    }
                    HSSFCell cell3 = row3.createCell(3);
                    cell3.setCellValue(StringUtils.getString(map.get("userName")));
                    HSSFCell cell4 = row3.createCell(4);
                    cell4.setCellValue(StringUtils.getString(map.get("create_at")));
                }
            }
            //5.输出
            workbook.write(out);
            workbook.close();
            return 1;
            //out.close();
        }catch(Exception e)
        {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     *
     * @param workbook
     * @param fontsize
     * @return 单元格样式
     */
    private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontsize) {
        // TODO Auto-generated method stub
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
        //创建字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontHeightInPoints(fontsize);
        //加载字体
        style.setFont(font);
        return style;
    }




}
